/*Using the STUDENT table in the MISLab1 database in Omnymbus, perform the following tasks:  Note 
the first SELECT is there to label the output, DUAL is a "dummy" table.  The second SELECT is the 
solution.

1.	Write a SQL statement to display Student’s First and Last Name.*/

SELECT ' Result 1 ' AS 'Result Table' from DUAL;

SELECT STUDENT.First_Name , STUDENT.Last_Name
FROM STUDENT;


/*2.	Write a SQL statement to display the Major of the STUDENT with no duplications. Do not 
display student names.*/

SELECT ' Result 2 '  AS 'Result Table' from DUAL;

SELECT Distinct STUDENT.Major
FROM STUDENT;
 

/*3.	Write a SQL statement to display the First and Last Name of students who live in the Zip code 
82622. */

SELECT ' Result 3 ' AS 'Result Table'  from DUAL;
SELECT STUDENT.First_Name , STUDENT.Last_Name
FROM STUDENT
WHERE STUDENT.ZIP ="82622";


/*4.	Write a SQL statement to display the First and Last Name of students who live in the Zip code 
97912 and have the major of CS.*/

SELECT ' Result 4 ' AS 'Result Table'  from DUAL;
SELECT STUDENT.First_Name , STUDENT.Last_Name
FROM STUDENT
WHERE STUDENT.ZIP ="82622" AND STUDENT.MAJOR="CS";

/*5.	Write a SQL statement to display the First and Last Name of students who live in the Zip code 
82622 or 37311. Do not use IN.*/

SELECT ' Result 5 ' AS 'Result Table'  from DUAL;
SELECT STUDENT.First_Name , STUDENT.Last_Name
FROM STUDENT
WHERE STUDENT.ZIP ="82622" OR STUDENT.ZIP ="37311";


/*6.	Write a SQL statement to display the First and Last Name of students who have the major of 
Business or Math. Use IN.*/

SELECT ' Result 6 ' AS 'Result Table'  from DUAL;
SELECT STUDENT.First_Name , STUDENT.Last_Name
FROM STUDENT
WHERE  STUDENT.MAJOR IN ("Business","Math");


/*7.       Write a SQL statement to display the First and Last Name of students who have the Class 
greater than 1 and less than 10.   Use the SQL command BETWEEN.	*/


SELECT ' Result 7 ' AS 'Result Table'  from DUAL;
SELECT STUDENT.First_Name , STUDENT.Last_Name
FROM STUDENT
WHERE  STUDENT.CLASS BETWEEN 1 AND 10;



/*8.	Write a SQL statement to display the First and Last Name of students who have a Last name 
that starts with an S.*/

SELECT ' Result 8 ' AS 'Result Table'  from DUAL;
SELECT STUDENT.First_Name , STUDENT.Last_Name
FROM STUDENT
WHERE  STUDENT.Last_Name LIKE "S%";



/*9.     Write a SQL statement to display the First and Last Name of students having an a in the second 
position of their first names.*/

SELECT ' Result 9 ' AS 'Result Table'  from DUAL;
SELECT STUDENT.First_Name , STUDENT.Last_Name
FROM STUDENT
WHERE  STUDENT.First_Name LIKE '_a';


 
/*10.	Write a SQL expression to display each Status and the number of occurrences of each status 
using the Count(*) function; display the result of the Count(*) function as CountStatus. Group by 
Status and display the results in escending order of CountStatus.*/

SELECT ' Result 10 ' AS 'Result Table'  from DUAL;
SELECT STUDENT.STATUS, Count(*) AS CountStatus
FROM  STUDENT
GROUP BY STUDENT.STATUS
ORDER BY CountStatus ASC;